package cn.edu.swu.book;

import cn.edu.swu.core.AbstractRepo;
import cn.edu.swu.core.ResultSetVisitor;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class BookRepo extends AbstractRepo {
    private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd");

    public List<Book> getAllBooks() throws SQLException, ClassNotFoundException {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book";
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                books.add(getOneRecord(rs));
            }
        });
        return books;
    }

    public void save(Book book) throws SQLException {
        if (book.getId() < 0) {
            this.add(book);
        } else {
            this.update(book);
        }
    }

    public void add(Book book) throws SQLException {
        String template = "INSERT INTO book (name, author, price, memo, publish, image) " +
                "values ('%s', '%s', %s, '%s', '%s', '%s')";
        this.execute(String.format(template, book.getName(), book.getAuthor(), book.getPrice(),
                book.getMemo(), dateFormat.format(book.getPublish()), book.getImage()));
    }

    public void update(Book book) throws SQLException {
        String template = "UPDATE book " +
                "SET name='%s', author='%s', price=%s, memo='%s', publish='%s', image='%s' " +
                "WHERE id=%s";
        this.execute(String.format(template, book.getName(), book.getAuthor(), book.getPrice(),
                book.getMemo(), dateFormat.format(book.getPublish()), book.getImage(), book.getId()));
    }

    public void deleteById(int id) throws SQLException {
        String template = "DELETE FROM book WHERE id = %s";
        this.execute(String.format(template, id));
    }

    public Book getById(int id) throws SQLException {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE id=" + id;
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                books.add(getOneRecord(rs));
            }
        });
        return books.size() > 0 ? books.get(0) : null;
    }

    public List<Book> search(String key) throws SQLException {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM book WHERE name LIKE '%" + key + "%' " +
                "OR author LIKE '%" + key + "%'";
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException {
                books.add(getOneRecord(rs));
            }
        });
        return books;
    }

    private Book getOneRecord(ResultSet rs) throws SQLException {
        Book book = new Book();
        book.setId(rs.getInt("id"));
        book.setName(rs.getString("name"));
        book.setAuthor(rs.getString("author"));
        book.setPrice(rs.getDouble("price"));
        book.setMemo(rs.getString("memo"));
        book.setPublish(rs.getDate("publish"));
        book.setImage(rs.getString("image"));
        return book;
    }



}
